Problem Description:¶
India is seeing an explosion of new competitors in the Broadband space. 'India Broadband' is a company that is now seeing a lot of customer churn due to customer dissatisfaction because of broadband outages.The company has now curated a dataset, where it tracks several variables that it believes impact the outage_duration. They have tracked three different outage durations, 0 for no outage, 1 for short outages that last anywhere between a few minutes and a maximum of 2 hours, and 2 for long outages that can last from 2 hours to sometimes even a couple of days.You will now have to use these metrics that the company has tracked to create a machine learning model that will be able to predict the outage_duration so that the company can better handle outages and improve customer satisfaction and therefore reduce customer churn.¶
Exploratory Data Analysis¶
Importing the required libararies¶
In [20]:
import pandas as pd
import numpy as np
import random
from sklearn.model_selection import train_test_split
from sklearn.model_selection import learning_curve, GridSearchCV
from sklearn.ensemble import ExtraTreesClassifier
import xgboost as xgb
import operator
from scipy.stats import uniform as sp_rand
from scipy.stats import randint as sp_randint
import timeit
import matplotlib.pyplot as plt
%matplotlib inline
import seaborn as sns
sns.set_style("whitegrid")
tic0=timeit.default_timer()
pd.options.mode.chained_assignment = None # default='warn'
There are 7 CSV files provided to us, they are described below:¶
● train_data.csv: It has a unique event id for each observation of the outage_duration in a particular area_code
● test_data.csv: Similar to the train dataset, we are provided with an id and an area_code, we are expected to predict the outage_duration for each of the records. (This will be provided to you later on 4th July)
● broadband_data.csv: For each of the event ids mentioned in the train_data.csv and test_data.csv files and also some additional ids there is a record of the broadband_type that is stored in the dataset. There are 10 different types of broadbands that are observed in the dataset
● outage_data.csv: For each of the event ids mentioned in the train_data.csv and test_data.csv files and also some additional ids there is a record of the outage_type that is stored in the dataset. There are 5 different outage_type's recorded in the dataset.
● report_data.csv: For each event id there are log_report_type and volume columns are recorded. log_report_type is a type of the recorded report generated by a technical team member after evaluating the outage. volume is the volume of data handled in the area at the time of report in custom company specific units.
● server_data.csv: For each of the event ids mentioned in the train_data.csv and test_data.csv files and also some additional ids there is a record of the transit_server_type that is stored in the dataset. Transit Servers handle the requests and responses of the customers.
● sample_submission.csv: The format of CSV file required for submission to the evaluation backend. (Please remember that the prediction file which you are going to upload to tool, to check out what is your score should be of the same format as this file)
Reading the data sets¶
In [2]:
train_data = pd.read_csv('/Users/nadeemm/Desktop/PHD B78/data/train_data.csv')
broadband_data = pd.read_csv('/Users/nadeemm/Desktop/PHD B78/data/broadband_data.csv')
outage_data = pd.read_csv('/Users/nadeemm/Desktop/PHD B78/data/outage_data.csv')
report_data = pd.read_csv('/Users/nadeemm/Desktop/PHD B78/data/report_data.csv')
server_data = pd.read_csv('/Users/nadeemm/Desktop/PHD B78/data/server_data.csv')
sample_data = pd.read_csv('/Users/nadeemm/Desktop/PHD B78/data/sample_submission.csv')
Looking into the shapes of all the data sets¶
In [3]:
train_data.shape, broadband_data.shape, outage_data.shape, report_data.shape, server_data.shape
Out[3]:
printing the shape of all data sets¶
In [4]:
print('The shape of broadband is: {}\n'.format(broadband_data.shape))
print('The shape of outage is: {}\n'.format(outage_data.shape))
print('The shape of report is: {}\n'.format(report_data.shape))
print('The shape of server is: {}\n'.format(server_data.shape))
print('The shape of train is: {}\n'.format(train_data.shape))
print('The shape of sample is: {}\n'.format(sample_data.shape))
---------------------------------------------------------------------● train_data.csv: It has a unique event id for each observation of the outage_duration in a particular area_code¶
In [5]:
train_data.head()
Out[5]:
Train Data has three columns:¶
- ID
- Area Code
- Outage Duration
In [6]:
train_data['area_code'].value_counts()
Out[6]:
Value counts of Train data : As we can see that Area_821 is repeated most of the times in the data¶
In [7]:
val=list(train_data['area_code'].value_counts())
for i in range(len(val)):
print("Area Code",train_data['area_code'].value_counts().index[i],round(val[i]/sum(val)*100),'%')
Plot for Outage Duration¶
In [8]:
#count plot for Outage Duration
plt.figure(figsize = (14,6))
sns.countplot(train_data['outage_duration'])
plt.tight_layout()
plt.show()
As there are three different Outage durations¶
- outage durations,
0for no outage - outage durations,
1for short outages that last anywhere between a few minutes and a maximum of 2 hours - outage durations,
2for long outages that can last from 2 hours to sometimes even a couple of days.
Plot with ID vs Area Code¶
In [9]:
import plotly.offline as pyo
import plotly.graph_objs as go
data = [go.Scatter(x=train_data['area_code'],
y=train_data['id'],
mode="markers",text=train_data["area_code"])]
layout=go.Layout(title="ID vs Area_code",xaxis={"title":"area_code"},yaxis=dict(title="ID"),hovermode="closest")
fig=go.Figure(data,layout)
fig.show()
From the plot most of the ID's are covered in particular area, As we can see in the graph, as the Number of users are reduces in some of the specific areas¶
---------------------------------------------------------------------Plot with ID and Outage Durations.¶
- All the three Outage Durations
In [10]:
import plotly.offline as pyo
import plotly.graph_objs as go
data = [go.Scatter(x=train_data.loc[train_data.outage_duration==0,'area_code'],
y=train_data.loc[train_data.outage_duration==0,'id'],
mode="markers",text=train_data["outage_duration"]),
go.Scatter(x=train_data.loc[train_data.outage_duration==1,'area_code'],
y=train_data.loc[train_data.outage_duration==1,'id'],
mode="markers",text=train_data["outage_duration"]),
go.Scatter(x=train_data.loc[train_data.outage_duration==2,'area_code'],
y=train_data.loc[train_data.outage_duration==2,'id'],
mode="markers",text=train_data["outage_duration"])]
layout=go.Layout(title="ID vs Area Code",xaxis={"title":"area_code"},yaxis=dict(title="ID"),hovermode="closest")
fig=go.Figure(data,layout)
fig.show()
Checking the number of outage_duration¶
- As we can see that there are three levels of outages
- 0 for NO outages : trace 0 -> Blue circles
- 1 for Few minutes to few hours : trace 1 -> Red circles
- 2 for Few Hours and more : trace 2 -> Green circles
By plotting the Data with respect to ID's and outage duration , we can see that the number of users and outage is evenlly distributed, It's like there is no specific user ID's are facing the outages, All number of users are facing the outages¶
Checking the Plot with respect to Outage Duration 0, In Area Vs ID¶
In [11]:
import plotly.offline as pyo
import plotly.graph_objs as go
data = [go.Scatter(x=train_data.loc[train_data.outage_duration==0,'area_code'],
y=train_data.loc[train_data.outage_duration==0,'id'],
mode="markers",text=train_data["outage_duration"])]
layout=go.Layout(title="ID vs Area Code with Outage Duration : 0",xaxis={"title":"area_code"},yaxis=dict(title="ID"),hovermode="closest")
fig=go.Figure(data,layout)
fig.show()
From the plot we see the most of the ID with Area are Not facing much problem¶
Checking the Plot with respect to Outage Duration 1, In Area Vs ID¶
In [12]:
import plotly.offline as pyo
import plotly.graph_objs as go
data = [go.Scatter(x=train_data.loc[train_data.outage_duration==1,'area_code'],
y=train_data.loc[train_data.outage_duration==1,'id'],
mode="markers",text=train_data["outage_duration"])]
layout=go.Layout(title="ID vs Area Code Outage Duration : 1",xaxis={"title":"area_code"},yaxis=dict(title="ID"),hovermode="closest")
fig=go.Figure(data,layout)
fig.show()
Checking the Plot with respect to Outage Duration 2, In Area Vs ID¶
In [13]:
import plotly.offline as pyo
import plotly.graph_objs as go
data = [go.Scatter(x=train_data.loc[train_data.outage_duration==2,'area_code'],
y=train_data.loc[train_data.outage_duration==2,'id'],
mode="markers",text=train_data["outage_duration"])]
layout=go.Layout(title="ID vs Area Code Outage Duration : 2",xaxis={"title":"area_code"},yaxis=dict(title="ID"),hovermode="closest")
fig=go.Figure(data,layout)
fig.show()
From the plot we can see that the few areas are having high outages compared to the other areas¶
Maximum Outage Duration With Respect to Area¶
In [14]:
train_data_outage = train_data.loc[train_data.outage_duration==2,'area_code'].value_counts()
In [15]:
train_data_outage
Out[15]:
In [16]:
train_data['outage_duration'].value_counts()
Out[16]:
In [17]:
#count plot for Outage Duration
plt.figure(figsize = (10,4))
sns.countplot(train_data['outage_duration'])
plt.tight_layout()
plt.show()
Outage for the users with percentage representations.¶
In [18]:
val=list(train_data['outage_duration'].value_counts())
for i in range(len(val)):
print("Outage type",train_data['outage_duration'].value_counts().index[i],round(val[i]/sum(val)*100),'%')
As we saw above the percentages of the Outage Durations, The visual representation of the same as we can see the number of counts of outages present in the data¶
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------¶
Reading the csv File¶
In [21]:
outage_data = pd.read_csv('/Users/nadeemm/Desktop/PHD B78/data/outage_data.csv')
outage_data.head()
Out[21]:
In [22]:
outage_data.shape
Out[22]:
There are two columns and 18852 rows of ID¶
- ID : 18552
- Outage type : There are 5 Different Outage_types
- shape : (18552,2)
Plotting the number of counts of Outage Type¶
In [23]:
#count plot for Outage Duration
plt.figure(figsize = (14,6))
sns.countplot(outage_data['outage_type'])
plt.tight_layout()
plt.show()
There are 5 different types from outage type 1 to 5.¶
- Outage type 2 and Outage typw 1 is most repeated in the data
In [24]:
outage_data['outage_type'].value_counts()
Out[24]:
Here we can see the value counts of each outage type
Let's look into the percentage of Outage type in the data¶
In [25]:
val=list(outage_data['outage_type'].value_counts())
for i in range(len(val)):
print(outage_data['outage_type'].value_counts().index[i],round(val[i]/sum(val)*100),'%')
Removing the text¶
In [26]:
outage_data.outage_type=outage_data.outage_type.apply(lambda x: int(x.split("_")[2]))
In [27]:
outage_data.head()
Out[27]:
Dummification of the Outage type¶
In [28]:
outage_type_dummies = pd.get_dummies(outage_data,columns=['outage_type'])
In [29]:
outage_type_dummies.head()
Out[29]:
In [30]:
outage_data.describe(include='all')
Out[30]:
From the above describtion.¶
- total count is 18552
- Outage type 2 is repeated the most
- There are 5 level's of Outage type
- Frequency is 8737, i.e., the Outage type has been repeated in the record
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------¶
Looking into the first 5 rows of the data¶
In [31]:
server_data = pd.read_csv('/Users/nadeemm/Desktop/PHD B78/data/server_data.csv')
server_data.head()
Out[31]:
In [32]:
server_data.shape
Out[32]:
In [33]:
server_data.nunique().value_counts()
Out[33]:
From the Server Data.¶
- there are two columns in the dataframe
- ID : 31170 and unique is 18552
- Server type : Total there are 53 Server's in the given data set
Checking the Value count of the server type.¶
In [34]:
server_count = server_data['transit_server_type'].value_counts()
server_count
Out[34]:
Checking the Value count with percentage wise¶
In [35]:
val=list(server_data['transit_server_type'].value_counts())
for i in range(len(val)):
print(server_data['transit_server_type'].value_counts().index[i],round(val[i]/sum(val)*100),'%')
Plotting with ID vs Transit Server Type¶
In [36]:
# x and y given as array_like objects
import plotly.express as px
fig = px.scatter(server_data['id'], server_data['transit_server_type'])
fig.show()
ID in X-axis and Transit server in Y-axis.¶
- In the scartted plot Few ID's are using few servers
- Likely Many Users are opting to very servers.
As from the above plot we can see.¶
- Server 11 has most users : 25% of users from the given data, i.e., 7888 times it has been repeated
- Server 35 has next number of users.
Top Five server Types used the most in the Given Data.¶
In [38]:
server_count.head()
Out[38]:
In [39]:
server_data.head()
Out[39]:
Let's Remove the text from the transit server type.¶
In [40]:
server_data.transit_server_type=server_data.transit_server_type.apply(lambda x: int(x.split("_")[3]))
After removing the text from the column transit_server_type, the top 10 rows¶
In [684]:
server_data.head(10)
Out[684]:
Plotting Server type Count¶
In [41]:
#count plot for transit server type
plt.figure(figsize = (14,6))
sns.countplot(server_data['transit_server_type'])
plt.tight_layout()
plt.show()
Exploring the Value Counts of the Server Data¶
In [42]:
server_data['transit_server_type'].value_counts().sum()
Out[42]:
In [43]:
server_data.shape
Out[43]:
Dummification of the server type as we know there are 53 server types.¶
In [44]:
server_type_dummies = pd.get_dummies(server_data,columns=['transit_server_type'])
In [45]:
server_type_dummies.head()
Out[45]:
In [46]:
server_type_dummies.shape
Out[46]:
After Dummification we get 53 new columns and with ID we have 54 columns and 31170 ID¶
In [47]:
server_data.describe(include='all')
Out[47]:
In [176]:
server_data.shape
Out[176]:
In [180]:
server_type_dummies.describe()
Out[180]:
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------¶
Report Data¶
● report_data.csv: For each event id there are log_report_type and volume columns are recorded. log_report_type is a type of the recorded report generated by a technical team member after evaluating the outage. volume is the volume of data handled in the area at the time of report in custom company specific units.¶
Reading the Report Data¶
In [48]:
report_data = pd.read_csv('/Users/nadeemm/Desktop/PHD B78/data/report_data.csv')
Taking a look at top five rows from the report data¶
In [49]:
report_data.head()
Out[49]:
In [50]:
report_data.shape
Out[50]:
In [51]:
report_data.nunique()
Out[51]:
Report Data: There are three columns¶
- ID : 58671
- Log_report_type: Has 386 log report customer service record issue
- Volume : There are 341 Volumes, for 10 minutes prior to the time of recording the observation as per custom company specific units.
Value Count for Log Report Type¶
In [52]:
log_report_ = report_data['log_report_type'].value_counts()
In [53]:
log_report_.head(10)
Out[53]:
Looking into the top 10 report's issued by the custombers, Frequently more in the given Data¶
Let's See the Percentage for the Log Report Type¶
In [54]:
val=list(report_data['log_report_type'].value_counts())
for i in range(len(val)):
print(report_data['log_report_type'].value_counts().index[i],round(val[i]/sum(val)*100),'%')
Plot for Log Report Type¶
In [55]:
#count plot for Log Report Type
plt.figure(figsize = (14,6))
sns.countplot(report_data['log_report_type'])
plt.tight_layout()
plt.show()
From the above plot without removing the text from the feature it's not able to see clearly¶
Removing the text from the Log Report Type column and keeping numerator alone¶
In [56]:
report_data.log_report_type=report_data.log_report_type.apply(lambda x: int(x.split("_")[3]))
In [57]:
report_data.head()
Out[57]:
In [58]:
#count plot for Log Report Type
plt.figure(figsize = (14,6))
sns.countplot(report_data['log_report_type'].value_counts())
plt.tight_layout()
plt.show()
The Above plot represents the number of reports in assending order of value counts in top order of log report by customber complents¶
Top records of log report type receved by the customer service representatives¶
In [59]:
log_report_.head(10)
Out[59]:
Reset of index and given it a new name¶
In [60]:
report_data.reset_index(inplace=True)
report_data.rename(columns={'index':'count_of_log_report_seen'},inplace=True)
The New Feature 'Count of Log report seen' is to map back to the orginal ID after creating new features and to check the feature created is properlly matched!!!¶
In [61]:
report_data.head()
Out[61]:
Creating New Feature with number of ID of log features¶
In [62]:
report_data_value_counts = report_data.log_report_type.value_counts().to_dict()
report_data['num_ids_with_log_feature'] = report_data['log_report_type'].map(lambda x: report_data_value_counts[x])
Creating new feature by mapping the log report type by using dictionary and put in the new feature¶
In [63]:
report_data.head()
Out[63]:
Manually binning the report data to map the ID¶
In [64]:
bins = [-10,0,10,20,30,40,50,60,70,80,90,100,110,120,130,140,150,160,170,180,190,
200,210,220,230,240,250,260,270,280,290,300,310,320,330,340,350,360,
370,380,390,400]
report_data['binned_log_report'] = np.digitize(report_data['log_report_type'], bins, right=True)
bins_offset = list(map(lambda x:x+5, bins))
report_data['binned_offset_log_report'] = np.digitize(report_data['log_report_type'], bins_offset, right=True)
As we have many levels in the log report type, we have done manual binning to reduce the complexity.¶
- Created new feature 'Binned log report'
np.digitize : Return the indices of the bins to which each value in input array belongs.
Another Feature for Binned offset log report
- Bins offset Not be more then 5, to keep in threshold.
In [65]:
report_data.head()
Out[65]:
Above we could see there is a lag of shift in the features binned so we have to position the log feature,¶
- We'll Create a column position log features and groupby the cummulitive sum
Creating Feature for lag position¶
In [66]:
report_data['position_of_log_report'] = 1
report_data['position_of_log_report'] = report_data.groupby(['id'])['position_of_log_report'].cumsum()
report_data['log_report_type'] = report_data['log_report_type'].astype(int)
- Initially assigning position of log report to 1
- Grouping the ID with respect to Position of log report with cummulative sum(Partially summing of sequence)
In [67]:
import plotly.offline as pyo
import plotly.graph_objs as go
data = [go.Scatter(x=report_data.loc[report_data.log_report_type,'log_report_type'],
y=train_data.loc[report_data.log_report_type,'id'],
mode="markers",text=report_data["log_report_type"])]
layout=go.Layout(title="ID vs Log Report Type",xaxis={"title":"Log Report Type"},yaxis=dict(title="ID"),hovermode="closest")
fig=go.Figure(data,layout)
fig.show()
From the plot as we have explored, The log report is varied with the ID user and log report¶
In [68]:
report_data.head()
Out[68]:
In [69]:
report_data.describe(include='all')
Out[69]:
In [177]:
report_data.shape
Out[177]:
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------¶
Reading the Broadband Data¶
In [70]:
broadband_data = pd.read_csv('/Users/nadeemm/Desktop/PHD B78/data/broadband_data.csv')
In [71]:
broadband_data.head()
Out[71]:
In [72]:
broadband_data.shape
Out[72]:
In [73]:
broadband_data.nunique()
Out[73]:
BroadBand Data:¶
- ID : 21076, Unique ID : 18552
- Broadband type : 10 Broadband types avaliable
Value Count for Broadband Data¶
In [74]:
broadband_data['broadband_type'].value_counts()
Out[74]:
Broadband types description¶
- broadband_type_8 : 'ADSL 1'
- broadband_type_2 : 'ADSL 2'
- broadband_type_6 : 'ADSL 2+'
- broadband_type_7 : 'Cable'
- broadband_type_4 : 'Fiber 1'
- broadband_type_9 : 'BPL'
- broadband_type_3 : 'Fiber 2'
- broadband_type_10 : 'Fiber High Speed'
- broadband_type_1 : 'Fiber Ultra'
- broadband_type_5 : 'Fiber Ultra Max'
** From the data Broadband type 8 : 'ADSL 1' is used more in the given data, then broadband type 2 and so onn.
Percentage of Broadband types through Value counts in the Data¶
In [75]:
val=list(broadband_data['broadband_type'].value_counts())
for i in range(len(val)):
print(broadband_data['broadband_type'].value_counts().index[i],round(val[i]/sum(val)*100),'%')
Plot for Broadband types¶
In [76]:
#count plot for broadband type
plt.figure(figsize = (14,6))
sns.countplot(broadband_data['broadband_type'])
plt.tight_layout()
plt.show()
From the plot we can see the usage of Broadband by the used as per the data given.¶
Removing the Text from the Broadband type and keeping numbers.¶
In [77]:
broadband_data.broadband_type=broadband_data.broadband_type.apply(lambda x: int(x.split("_")[2]))
In [78]:
broadband_data
Out[78]:
In [79]:
import plotly.offline as pyo
import plotly.graph_objs as go
data = [go.Scatter(x=broadband_data.id,
y=broadband_data.broadband_type,
mode="markers",text=broadband_data["broadband_type"])]
layout=go.Layout(title="ID vs Broadband Type",xaxis={"title":"ID"},yaxis=dict(title="Broadband Type"),hovermode="closest")
fig=go.Figure(data,layout)
fig.show()
In [80]:
broadband_data.head()
Out[80]:
Dummification of Broadband type¶
In [81]:
broadband_type_dummies = pd.get_dummies(broadband_data,columns=['broadband_type'])
In [82]:
broadband_type_dummies
Out[82]:
After Dummifing the broadband type we get (21076x11) columns¶
In [83]:
val=list(broadband_data['broadband_type'].value_counts())
for i in range(len(val)):
print(broadband_data['broadband_type'].value_counts().index[i],round(val[i]/sum(val)*100),'%')
As we can see that Broadband type 8 : ADSL1 is used more in the data¶
- After Binning the data, Found that broadband 8 must be put in different columns by shifting 1 or lag 01
Creating new feature Broadband Shifted up¶
- As we can see that after dummification broadband type 8 is repeated 49% of time and broadband type 2 is has 42%
- To make Data more representative we're shifting it to one
In [84]:
broadband_data['broadband_shifted_up'] = broadband_data['broadband_type'].shift(1)
In [85]:
broadband_data['broadband_shifted_up'] = broadband_data['broadband_shifted_up'].astype('float64','ignore')
In [86]:
broadband_data.head()
Out[86]:
After the shift we're getting NaN¶
- we need to fill the NaN
In [87]:
broadband_data.dtypes
Out[87]:
In [88]:
broadband_data['broadband_type']=broadband_data['broadband_type'].astype('int64')
In [89]:
broadband_data.isnull().sum()
Out[89]:
We can see that above feature has NaN so we should fill the data with broadband type 8 as it's repeated more.¶
In [90]:
broadband_data['broadband_shifted_up'].fillna(8,inplace=True)
In [91]:
broadband_data.head()
Out[91]:
In [92]:
broadband_data.describe()
Out[92]:
Let's check the next Broadband type repeated or it's Different from the previous one!¶
- Create two new features or the repeated and different type!!
In [93]:
broadband_data['is_next_broadband_type_repeat'] = 0
broadband_data['is_next_broadband_type_different'] = 0
In [94]:
broadband_data.head()
Out[94]:
In [95]:
repeat_cond = broadband_data['broadband_shifted_up'] == broadband_data['broadband_type']
In [96]:
repeat_cond.value_counts
Out[96]:
Creating New Feature is next broadband type repeated¶
In [97]:
broadband_data['is_next_broadband_type_repeat'][repeat_cond] = 1
In [98]:
broadband_data.head()
Out[98]:
In [99]:
broadband_data['is_next_broadband_type_repeat'][~repeat_cond] = 1 # ~ Binary Ones Complement
In [100]:
broadband_data.head()
Out[100]:
Creating new feature switches broadband type¶
- Return the cumulative sum of the elements along a given axis. (cumsum())
In [101]:
broadband_data['switches_broadband_type'] = broadband_data['is_next_broadband_type_different'].cumsum()
In [102]:
broadband_data['switches_broadband_type'].value_counts()
Out[102]:
In [103]:
broadband_data
Out[103]:
In [104]:
broadband_data.nunique().value_counts
Out[104]:
There are no repeatation in the dummified data, we can drop the features.¶
In [105]:
broadband_data.describe()
Out[105]:
In [106]:
broadband_data.dtypes
Out[106]:
In [178]:
broadband_data.shape
Out[178]:
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------¶
Merging the files¶
Checking the Unique ID's From all the Data set's¶
In [107]:
print("Number of unique ID's in Train Data : ",train_data.id.nunique())
print("Number of unique ID's in Broadband Data : ",broadband_data.id.nunique())
print("Number of unique ID's in Server Data : ",server_data.id.nunique())
print("Number of unique ID's in Outage Data : ",outage_data.id.nunique())
print("Number of unique ID's in Report Data : ",report_data.id.nunique())
Printing the Shape of all the Data Set's¶
In [108]:
print('The shape of train is: {}\n'.format(train_data.shape))
print('The shape of broadband is: {}\n'.format(broadband_data.shape))
print('The shape of server is: {}\n'.format(server_data.shape))
print('The shape of outage is: {}\n'.format(outage_data.shape))
print('The shape of report is: {}\n'.format(report_data.shape))
Merging the Train Data with Broadband Data¶
In [109]:
train_broadband_combined = pd.merge(train_data,broadband_data,left_on = ['id'],
right_on = ['id'],how='left')
In [110]:
train_broadband_combined.shape
Out[110]:
In [111]:
train_broadband_combined.head()
Out[111]:
Answering the Questions¶
● Which areas are most prone to long outage durations?
● Which broadband types are suspect of long outage durations?
● Any other recommendations to improve the detection of outage durations.
To answer the question!!¶
- We have merged the train data with broadband data
In [112]:
train_broadband_outage = train_broadband_combined.loc[train_broadband_combined.outage_duration==2,'broadband_type'].value_counts()
In [113]:
train_broadband_outage
Out[113]:
Plot for Broadband types¶
In [114]:
#count plot for broadband type
plt.figure(figsize = (14,6))
sns.countplot(train_broadband_combined['broadband_type'])
plt.tight_layout()
plt.show()
From the Above plot Broadband type : 8 is more in the Data¶
Creating an Long outage my taking outage duration = 2 alone!!¶
In [117]:
long_outage = train_data['outage_duration']==2
long_outage.head()
Out[117]:
In [118]:
long_outage_ = train_data[long_outage]
In [119]:
long_outage_.head()
Out[119]:
Plot with Area code vs Outage Duration¶
In [120]:
count=long_outage_.area_code.value_counts()
count.head(10).plot(kind="bar",figsize=(10,10))
count.head(10)
Out[120]:
From the above graph we clearly see that Area_1100 has more Outages and is more Prone to outages!!!¶
Defining a function to remove the text from the area code¶
In [121]:
def str_to_num(string):
return int(string.split("_")[1])
In [122]:
train_data["area_code"]=train_data["area_code"].apply(str_to_num)
In [123]:
train_data.head()
Out[123]:
Merging the Train Data with Outage Data¶
In [125]:
merge_1 = train_data.merge(outage_data,how="left",left_on=["id"],right_on=["id"])
In [126]:
merge_1.head()
Out[126]:
In [127]:
merge_1.set_index("id",inplace=True)
In [128]:
merge_1.describe()
Out[128]:
Creating a new feature to group by area code and outage duration¶
In [129]:
merge_1["num"]=merge_1.groupby(['area_code',"outage_duration"]).cumcount()+1
In [130]:
merge_1[(merge_1["area_code"]==1) & (merge_1["outage_duration"]==1)]
Out[130]:
Number Num represents number of outages of each type in an area to understand in which area what kind of outage is more so that we can focus on reducing it¶
In [131]:
merge_1.num.value_counts()
Out[131]:
Plot with respect to Number of Cases registered¶
In [132]:
data=[go.Histogram(x=merge_1["num"])]
layout=go.Layout(title="Histogram")
layout=go.Layout(title="number of cases",xaxis={"title":"number of cases"},yaxis=dict(title="count of no of cases"),hovermode="closest")
fig=go.Figure(data,layout)
fig.show()
we can understand how many cases are common in an area like in general, we can find minimum of 3 cases there in 586 areas.¶
Plot with respect to Number of cases vs Area¶
In [133]:
import plotly.offline as pyo
import plotly.graph_objs as go
data = [go.Scatter(x=merge_1.loc[merge_1.outage_duration==0,'area_code'],
y=merge_1.loc[merge_1.outage_duration==0,'num'],
mode="markers",text=merge_1["outage_duration"]),
go.Scatter(x=merge_1.loc[merge_1.outage_duration==1,'area_code'],
y=merge_1.loc[merge_1.outage_duration==1,'num'],
mode="markers",text=merge_1["outage_duration"]),
go.Scatter(x=merge_1.loc[merge_1.outage_duration==2,'area_code'],
y=merge_1.loc[merge_1.outage_duration==2,'num'],
mode="markers",text=merge_1["outage_duration"])]
layout=go.Layout(title="num vs area_code",xaxis={"title":"area_code"},yaxis=dict(title="num"),hovermode="closest")
fig=go.Figure(data,layout)
fig.show()
To understand in which area, which kind of outage is more, So that we can focus on reducing it, i.e., to get more customer churn in an area we should concentrate on which kind of outage type.¶
Merging Train Data with Server Data¶
In [135]:
server_data
Out[135]:
In [136]:
merge_2=server_data.merge(train_data, on='id')
In [137]:
merge_2.head()
Out[137]:
Checking for Any Null values¶
In [567]:
merge_2.isnull().sum()
Out[567]:
Checking for Unique Values¶
In [138]:
merge_2.nunique()
Out[138]:
In [139]:
merge_2.shape
Out[139]:
Assigning values counts to the DataFrame!!¶
In [140]:
transit_server_type_unq=pd.DataFrame(merge_2.transit_server_type.value_counts())
transit_server_type_unq.head()
Out[140]:
Creating New Column to check with Percentage transit Server Type¶
In [141]:
transit_server_type_unq["percen_trn"]=merge_2.transit_server_type.value_counts(normalize=True)*100
In [142]:
transit_server_type_unq
Out[142]:
In [143]:
merge_2.head()
Out[143]:
Creating New Feature Num to Group by Server Type and Area Code¶
In [144]:
merge_2["num"]=merge_2.groupby(['transit_server_type','area_code']).cumcount()+1
In [145]:
merge_2.head()
Out[145]:
Plotting Number of Servers vs Area Code¶
In [147]:
import plotly.offline as pyo
import plotly.graph_objs as go
data = [go.Scatter(x=merge_2.loc[:,'area_code'],
y=merge_2.loc[:,'num'],
mode="markers")]
layout=go.Layout(title="Number of Servers vs Area Code",xaxis={"title":"area_code"},yaxis=dict(title="number of servers"),hovermode="closest")
fig=go.Figure(data,layout)
fig.show()
From the above plot, To understand how Servers are divided with respect to Area-wise¶
Merging Train Data with Broadband Data¶
In [148]:
train_data_ = pd.read_csv('/Users/nadeemm/Desktop/PHD B78/data/train_data.csv')
broadband_data_ = pd.read_csv('/Users/nadeemm/Desktop/PHD B78/data/broadband_data.csv')
In [156]:
train_broadband_combined_ = pd.merge(train_data,broadband_data_,left_on = ['id'],
right_on = ['id'],how='left')
Assigning Max Outage Duration : 2 to Long Outage¶
In [157]:
long_outage_2 = train_broadband_combined_['outage_duration']==2
long_outage_2.head()
Out[157]:
Taking Long Outage 2.¶
In [158]:
long_outage_2_=train_broadband_combined_[long_outage_2]
In [159]:
long_outage_2_.head()
Out[159]:
Plotting the Broadband with Max Outage Duration¶
In [161]:
broad_band=long_outage_2_.broadband_type.value_counts()
broad_band.head(10).plot(kind="bar",figsize=(10,10))
broad_band.head(10)
Out[161]:
From the Above Plot, It is clear that Broadband type 8 has Highest and longest Outage Duration¶
Merging Train Data with Server Data¶
In [162]:
train_data_ = pd.read_csv('/Users/nadeemm/Desktop/PHD B78/data/train_data.csv')
server_data_ = pd.read_csv('/Users/nadeemm/Desktop/PHD B78/data/server_data.csv')
In [163]:
train_server_combined_1 = pd.merge(train_data_,server_data_,left_on = ['id'],
right_on = ['id'],how='left')
In [164]:
train_server_combined_1
Out[164]:
Assigning Outage 2 to long outage¶
In [165]:
long_outage_3 = train_server_combined_1['outage_duration']==2
long_outage_3.head()
Out[165]:
In [166]:
long_outage_3_=train_server_combined_1[long_outage_3]
In [167]:
long_outage_3_.head()
Out[167]:
Plotting Server type with Outage type¶
In [168]:
server_outage=long_outage_3_.transit_server_type.value_counts()
server_outage.head(10)
server_outage.head(10).plot(kind="bar",figsize=(10,10))
Out[168]:
Recommendation for detecting the Outage duration is by looking at the reports (log_report_type) generated by the customers.¶
Merging Data¶
In [173]:
train_broadband_outage.shape
Out[173]:
In [174]:
train_data.head(), outage_data.head(), broadband_data.head(), server_data.head(), report_data.head()
Out[174]:
In [181]:
train_data.shape, broadband_data.shape, outage_data.shape, report_data.shape, server_type_dummies.shape
Out[181]:
Merge 01 with train and Server data¶
In [182]:
train_server_merge_1 = pd.merge(train_data,server_type_dummies,left_on = ['id'],
right_on = ['id'],how='left')
In [183]:
train_server_merge_1.head(),train_server_merge_1.shape
Out[183]:
Merge 02 : Train with Outage Data¶
In [184]:
train_outage_merge_02 = pd.merge(train_data,outage_data,left_on = ['id'],
right_on = ['id'],how='left')
In [185]:
train_outage_merge_02.head(), train_outage_merge_02.shape
Out[185]:
Merge 03 : Train with Report Data¶
In [186]:
train_report_merge_03 = pd.merge(train_data,report_data,left_on = ['id'],
right_on = ['id'],how='left')
In [187]:
train_report_merge_03.head(),train_report_merge_03.shape
Out[187]:
Merge 04 : Train Data with Broadband Data¶
In [188]:
train_broadband_merge_04 = pd.merge(train_data,broadband_data,left_on = ['id'],
right_on = ['id'],how='left')
In [189]:
train_broadband_merge_04.head(), train_broadband_merge_04.shape
Out[189]:
Merging with Train, Server and Outage Individually¶
In [190]:
train_server_outage = pd.merge(train_server_merge_1,train_outage_merge_02,left_on = ['id'],
right_on = ['id'],how='left')
In [191]:
train_server_outage.head(), train_server_outage.shape
Out[191]:
Merging Train Data, Broadband Data, Report Data¶
In [192]:
train_broadband_report = pd.merge(train_broadband_merge_04,train_report_merge_03,left_on = ['id'],
right_on = ['id'],how='left')
In [193]:
train_broadband_report.head(), train_broadband_report.shape
Out[193]:
In [ ]:
### Final Merge
In [194]:
final_merge = pd.merge(train_server_outage,train_broadband_report,left_on = ['id'],
right_on = ['id'],how='left')
In [195]:
final_merge.head()
Out[195]:
In [196]:
final_merge.shape
Out[196]:
In [202]:
final_merge.nunique().value_counts
Out[202]:
In [205]:
type_=pd.crosstab(final_merge['outage_duration_x_x'],final_merge['outage_type'])
type_.plot(kind="bar", stacked=True, figsize=(8,6))
type_
Out[205]:
In [207]:
dat=pd.crosstab(final_merge['broadband_type'],final_merge['outage_duration_x_x'])
dat.plot(kind="bar", stacked=True, figsize=(8,6))
dat
Out[207]:
In [ ]: